﻿Public Class perOrdemServico
    Inherits perPadrao

#Region "Declaração de variáveis"
    Private objBuscaId As New perProximoId
#End Region

#Region "Propriedades"

    Public ReadOnly Property sqlConsulta() As String
        Get
            Dim sSql As String
            sSql = "    SELECT OS.NumOS, OS.IdOrdem, OS.DtEntrada,Clientes.RazaoSocial" & vbCrLf
            sSql &= "      FROM DC_OrdensServico OS " & vbCrLf
            sSql &= "INNER JOIN DC_Clientes Clientes" & vbCrLf
            sSql &= "        ON Clientes.Idcliente = OS.IDCliente"
            sSql &= "  ORDER BY OS.DtEntrada DESC"

            Return sSql
        End Get
    End Property

#End Region

#Region "Métodos"

    Public Function inserirOS(ByVal IDCliente As Int32, ByVal IDContato As Int32, ByVal IDUsuario As Int32, _
                              ByVal DtEntrada As Date, ByVal DtSaida As Date, ByVal DtConclusao As Date, _
                              ByVal NumOS As String) As Int32

        Dim iIDOrdem As Int32
        Dim sSql As String

        Try
            iIDOrdem = Me.objBuscaId.BuscaID("IDOrdem", "DC_OrdensServico")

            sSql = "  INSERT INTO DC_OrdensServico(" & vbCrLf
            sSql &= "                             IDOrdem, " & vbCrLf
            sSql &= "                             IDCliente, " & vbCrLf
            sSql &= "                             IDContato, " & vbCrLf
            sSql &= "                             IDUsuarioResp, " & vbCrLf
            sSql &= "                             DtEntrada, " & vbCrLf
            sSql &= "                             DtSaida, " & vbCrLf
            sSql &= "                             DtBaixa, " & vbCrLf
            sSql &= "                             NumOS) " & vbCrLf
            sSql &= "                     VALUES(" & vbCrLf
            sSql &= "                             @IdOrdem, " & vbCrLf
            sSql &= "                             @IdCliente, " & vbCrLf
            sSql &= "                             @IdContato, " & vbCrLf
            sSql &= "                             @IdUsuario, " & vbCrLf
            sSql &= "                             @DtEntrada, " & vbCrLf
            sSql &= "                             @DtSaida, " & vbCrLf
            sSql &= "                             @DtBaixa, " & vbCrLf
            sSql &= "                             @NumOS " & vbCrLf
            sSql &= " ) " & vbCrLf

            With MyBase.SQLCmd.Parameters
                .Clear()
                .AddWithValue("@IdOrdem", iIDOrdem)
                .AddWithValue("@IdCliente", IDCliente)
                .AddWithValue("@IdContato", IDContato)
                .AddWithValue("@IdUsuario", IDUsuario)
                .AddWithValue("@NumOS", NumOS.Trim)
                .AddWithValue("@DtEntrada", DtEntrada.Date)
                .AddWithValue("@DtSaida", DtSaida.Date)
                .AddWithValue("@DtBaixa", IIf((DtConclusao.Date = Date.MinValue), DBNull.Value, DtConclusao.Date))
            End With

            MyBase.executarAcao(sSql)
            Return iIDOrdem
        Catch ex As Exception
            Throw New Exception("Exceção no método 'inserirOS'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function

    Public Sub alterarOrdemServico(ByVal iIDOrdem As Int32, ByVal IDCliente As Int32, _
                                   ByVal IDContato As Int32, ByVal IDUsuario As Int32, _
                                   ByVal DtEntrada As Date, ByVal DtSaida As Date, _
                                   ByVal DtBaixa As Date, ByVal NumOs As String)

        Dim sSql As String

        Try

            sSql = "  UPDATE DC_OrdensServico SET IDCliente = " & IDCliente & "," & vbCrLf
            sSql &= "                            IDContato = " & IDContato & ", " & vbCrLf
            sSql &= "                            IDUsuarioResp = " & IDUsuario & ", " & vbCrLf
            sSql &= "                            DtEntrada = '" & DtEntrada.Date & "', " & vbCrLf
            sSql &= "                            DtSaida = '" & DtSaida.Date & "', " & vbCrLf
            sSql &= "                            DtBaixa = '" & DtBaixa.Date & "'," & vbCrLf
            sSql &= "                            NumOs = '" & NumOs.Trim & "'" & vbCrLf
            sSql &= " WHERE IDOrdem = " & iIDOrdem

            MyBase.executarAcao(sSql)

        Catch ex As Exception
            Throw New Exception("Exceção no método 'alterarOrdemServico'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Sub

    Public Sub excluirOrdemServico(ByVal iIDOrdem As Int32)

        Dim sSql As String

        Try
            sSql = "  DELETE DC_OrdensServico "
            sSql &= " WHERE IDOrdem = " & iIDOrdem

            MyBase.executarAcao(sSql)

        Catch ex As Exception
            Throw New Exception("Exceção no método 'excluirOrdemServico'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Sub

    Public Function selecionarCampoOrdem(ByVal sCampo As String, _
                                          ByVal iIDOrdem As Long) As DataTable
        Dim sSql As String
        Dim dsDados As New DataSet

        Try
            sSql = " SELECT " & sCampo
            sSql &= " FROM DC_OrdensServico "

            If (iIDOrdem > 0) Then
                sSql &= " WHERE IDOrdem = " & iIDOrdem
            End If

            dsDados = MyBase.executarConsulta(sSql, "DC_Status")

            If Not dsDados Is Nothing AndAlso dsDados.Tables("DC_Status").Rows.Count > 0 Then
                Return dsDados.Tables("DC_Status")
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw New Exception("Exceção no método 'selecionarCampoOrdem'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function

    Public Function selecionarOrdem(ByVal iIdOrdem As Int32) As DataRow
        Dim sSql As String
        Dim dsDados As New DataSet

        Try

            sSql = "    SELECT DC_OrdensServico.IdOrdem, DC_OrdensServico.NumOS, DC_OrdensServico.IDCliente, DC_Clientes.RazaoSocial," & vbCrLf
            sSql &= "           DC_OrdensServico.DtEntrada, DC_OrdensServico.DtSaida, DC_OrdensServico.DtBaixa," & vbCrLf
            sSql &= "           DC_OrdensServico.IdContato, DC_Contatos.NomeContato, " & vbCrLf
            sSql &= "           DC_OrdensServico.IdUsuarioResp, DC_Usuarios.Nome as NomeUsuario" & vbCrLf
            sSql &= "      FROM DC_OrdensServico" & vbCrLf
            sSql &= "INNER JOIN DC_Clientes " & vbCrLf
            sSql &= "        ON DC_OrdensServico.IDCliente = DC_Clientes.IDCliente" & vbCrLf
            sSql &= "LEFT JOIN DC_Contatos " & vbCrLf
            sSql &= "       ON DC_OrdensServico.IdContato  = DC_Contatos.IdContato" & vbCrLf
            sSql &= "LEFT JOIN DC_Usuarios " & vbCrLf
            sSql &= "       ON DC_OrdensServico.idUsuarioResp = DC_Usuarios.idUsuario" & vbCrLf
            If (iIdOrdem > 0) Then
                sSql &= "    WHERE DC_OrdensServico.IdOrdem = @IdOrdem"
            End If

            With MyBase.SQLCmd.Parameters
                .Clear()
                .AddWithValue("@IdOrdem", iIdOrdem)
            End With

            dsDados = MyBase.executarConsulta(sSql, "DC_OS")

            If Not dsDados Is Nothing AndAlso dsDados.Tables("DC_OS").Rows.Count > 0 Then
                Return dsDados.Tables("DC_OS").Rows(0)
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw New Exception("Exceção no método 'selecionarStatus'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function
#End Region
End Class
